An Example of a Database
In this lesson we will take a look at a simple university database.
We'll cover the following
Before we move onto the example, we must identify the need to store data in tabular form.
One of the main reasons to store data regarding a specific object in a table is ease of understanding. So, instead of keeping relevant information regarding an object (like a student) in separate files, tables allow us to keep the important data regarding that object (like student ID, name, address, age, etc.) in one place.
A table is a collection of related data held in a table format within a database. It consists of columns and rows.
| Column_1 | Column_2 | Column_3 |
|---|---|---|
| Data_Item1 | Data_Item2 | Data_Item3 |
| Data_Item4 | Data_Item5 | Data_Item6 |
| … | … | … |
Each row in a relational database represents one instance of the type of object described in that table. A row is also called a record. While the columns in a table are the set of facts that we keep track of regarding that type of object. A column is also called an attribute.
A university database example#
Let’s consider a university database that is used to maintain information concerning students, courses, and departments in a university environment. The diagram below shows the database structure and a few sample data records.
Student Table
| ID | First_Name | Last_Name | Class | Major |
|---|---|---|---|---|
| 2001 | Adam | Smith | Junior | CS |
| 2342 | Jonathan | Joestar | Sophomore | Economics |
| 2343 | Lucas | Klein | Senior | Physics |
Course Table
| Course_ID | Course_Name | Course_credits |
|---|---|---|
| CS200 | Intro to programming | 4 |
| MATH100 | Calculus-I | 3 |
| CS300 | Advanced Programming | 3 |
Department Table
| Department_Code | Department_Name |
|---|---|
| 1 | Computer Science |
| 2 | Electrical Engineering |
| 3 | Physics |
| 4 | Biology |
Instructor Table
| Instructor_ID | Instructor_fname | Department_Code |
|---|---|---|
| 12 | Sam | 1 |
| 22 | Tom | 2 |
| 04 | David | 3 |
Grade Table
| ID | Course_ID | Grade |
|---|---|---|
| 2001 | CS200 | A- |
| 2220 | EE100 | C |
| 2343 | PHY220 | B |
This database is comprised of five tables, each of which stores data records of the same type. The STUDENT table stores data on each student like the student’s name, identification (ID) number, etc. While the COURSE table contains information about each particular course.
To properly define this database, we must specify the structure of the rows of each table by specifying the different types of data elements to be stored in each row. In the above diagram, each row in the STUDENT table includes data that represent the name, ID, major, etc. of a single student only. Similarly, each row in the COURSE table includes data that represent the name, ID, and credit hours of a single course.
We also notice that the data in each column is of a specific type. For example, we observe that the name of a student in the STUDENT table is a string of alphabetic characters, while the ID number of a student is an integer, and so on.
Furthermore, to construct the university database, we store data to represent each student, course, instructor, and department as a row in the appropriate table. It is important to note that some of the records in different tables are related to each other. For example, we can see in the INSTRUCTOR table that the first instructor, Sam, is from the computer science department as the Department_Code is common between the two tables.
Finally, after we have defined and constructed the database we can use the DBMS to retrieve specific rows from the different tables. For example, we can retrieve the names of professors that belong to a particular department.
In the next lesson, we will dive deep into the characteristics of databases.
